﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
using System.Data.SqlClient;
using System.Data;


namespace StuInfoDAL
{
    public class StuInfoListDAL
    {
        public static List<StuInfo> Select(string name = "")
        {
            var sql = "select * from StuInfo where name like @name";
            SqlParameter[] sqlParameters = new SqlParameter[] {
            new SqlParameter("@name",$"%{name}%")
            };
            DataTable table = sqlHelper.Query(sql, sqlParameters);
            List<StuInfo> stuInfos = new List<StuInfo>();
            foreach (DataRow row in table.Rows)
            {
                StuInfo stuInfo = new StuInfo()
                {
                    Comment = row["Comment"].ToString(),
                    Id = Convert.ToInt32(row["Id"]),
                    Name = row["Name"].ToString(),
                    Sex = row["Sex"].ToString()
                };
                stuInfos.Add(stuInfo);
            }
            return stuInfos;
        }

        public static StuInfo Read(int id)
        {
            var sql = "select * from StuInfo where id = @id";
            SqlParameter[] sqlParameters = new SqlParameter[] {
            new SqlParameter("@id",id)
            };
            SqlDataReader reader = sqlHelper.Read(sql, sqlParameters);
            StuInfo stuInfo = null;
            if (reader.Read())
            {
                stuInfo = new StuInfo()
                {
                    Comment = reader["Comment"].ToString(),
                    Id = Convert.ToInt32(reader["Id"]),
                    Name = reader["Name"].ToString(),
                    Sex = reader["Sex"].ToString()
                };
            }
            reader.Close();
            return stuInfo;
        }

        public static int Delete(int id)
        {
            var sql = "delete from StuInfo where id = @id";
            SqlParameter[] sqlParameters = new SqlParameter[] {
            new SqlParameter("@id",id)
            };
            int num = sqlHelper.NonQuery(sql, sqlParameters);
            return num;
        }

        public static int Update(StuInfo info)
        {
            var sql = "update StuInfo set name=@name,Comment= @Comment ,sex = @sex where id = @id";
            SqlParameter[] sqlParameters = new SqlParameter[] {
            new SqlParameter("@id",info.Id),
            new SqlParameter("@name",info.Name),
            new SqlParameter("@Comment",info.Comment),
            new SqlParameter("@sex",info.Sex)
            };
            int num = sqlHelper.NonQuery(sql, sqlParameters);
            return num;
        }

        public static int Add(StuInfo info)
        {
            var sql = "insert into StuInfo values(@name,@sex,@Comment)";
            SqlParameter[] sqlParameters = new SqlParameter[] {
            new SqlParameter("@name",info.Name),
            new SqlParameter("@Comment",info.Comment),
            new SqlParameter("@sex",info.Sex)
            };
            int num = sqlHelper.NonQuery(sql, sqlParameters);
            return num;
        }

    }
}
